import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.jupiter.api.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class complate {
    //增删改查
    //1.查询所有 SQL:select * from tb_brand;
//    public void testSelectAll()throws Exception{
//    //1. 获取Connection
//    //3. 加载配置文件
//    Properties prop = new Properties();
//    prop.load(new FileInputStream("C:/Users/Administrator.DESKTOP-88TF7H0/IdeaProjects/JDBC/JDBC_demo/src/druid.properties"));
//    //4. 获取连接池对象
//    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//
//    //5. 获取数据库连接 Connection
//    Connection conn = dataSource.getConnection();
//
//    //2. 定义SQL
//    String sql = "select * from tb_brand;";
//
//    //3. 获取pstmt对象
//    PreparedStatement pstmt = conn.prepareStatement(sql);
//
//    //4. 设置参数
//
//    //5. 执行SQL
//    ResultSet rs = pstmt.executeQuery();
//
//    //6. 处理结果 List<Brand> 封装Brand对象，装载List集合
//    Brand brand = null;
//    List<Brand> brands = new ArrayList<>();
//    while (rs.next()){
//        //获取数据
//        int id = rs.getInt("id");
//        String brandName = rs.getString("brand_name");
//        String companyName = rs.getString("company_name");
//        //封装Brand对象
//        brand = new Brand();
//        brand.setId(id);
//        brand.setBrandName(brandName);
//        brand.setCompanyName(companyName);
//
//
//        //装载集合
//        brands.add(brand);
//
//    }
//    System.out.println(brands);
//    //7. 释放资源
//    rs.close();
//    pstmt.close();
//    conn.close();


//public void testAdd()throws Exception{
//    String brandName = "香飘飘";
//    String companyName = "香飘飘";
//    //1. 获取Connection
//
//    //3. 加载配置文件
//    Properties prop = new Properties();
//    prop.load(new FileInputStream("C:/Users/Administrator.DESKTOP-88TF7H0/IdeaProjects/JDBC/JDBC_demo/src/druid.properties"));
//    //4. 获取连接池对象
//    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//
//    //5. 获取数据库连接 Connection
//    Connection conn = dataSource.getConnection();
//
//    //2. 定义SQL
//    String sql = "insert into tb_brand(brand_name, company_name) values(?,?);";
//
//    //3. 获取pstmt对象
//    PreparedStatement pstmt = conn.prepareStatement(sql);
//
//    //4. 设置参数
//    pstmt.setString(1,brandName);
//    pstmt.setString(2,companyName);
//    //5. 执行SQL
//    int count = pstmt.executeUpdate(); // 影响的行数
//
//    System.out.println(count > 0);
//
//    //7. 释放资源
//
//    pstmt.close();
//    conn.close();

/**
 * 修改
 * 1. SQL：

 update tb_brand
 set brand_name  = ?,
 company_name= ?,
 ordered     = ?,
 description = ?,
 status      = ?
 where id = ?



 * 2. 参数：需要，所有数据
 * 3. 结果：boolean
 */

//@Test
//public void testmodify() throws Exception {
//    // 接收页面提交的参数
//    String brandName = "臭飘飘";
//    String companyName = "臭飘飘";
//    int id = 3;
//    //1. 获取Connection
//    //3. 加载配置文件
//    Properties prop = new Properties();
//    prop.load(new FileInputStream("C:/Users/Administrator.DESKTOP-88TF7H0/IdeaProjects/JDBC/JDBC_demo/src/druid.properties"));
//    //4. 获取连接池对象
//    DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//
//    //5. 获取数据库连接 Connection
//    Connection conn = dataSource.getConnection();
//
//    //2. 定义SQL
//    String sql = " update tb_brand\n" +
//            "         set brand_name  = ?,\n" +
//            "         company_name= ?\n" +
//            "            where id = ?";
//
//    //3. 获取pstmt对象
//    PreparedStatement pstmt = conn.prepareStatement(sql);
//
//    //4. 设置参数
//    pstmt.setString(1,brandName);
//    pstmt.setString(2,companyName);
//    pstmt.setInt(3,id);
//
//    //5. 执行SQL
//    int count = pstmt.executeUpdate(); // 影响的行数
//    //6. 处理结果
//    System.out.println(count > 0);
//
//
//    //7. 释放资源
//    pstmt.close();
//    conn.close();

    /**
     * 删除
     * 1. SQL：

     delete from tb_brand where id = ?

     * 2. 参数：需要，id
     * 3. 结果：boolean
     */

    @Test
    public void testDeleteById() throws Exception {
        // 接收页面提交的参数

        int id = 3;


        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("C:/Users/Administrator.DESKTOP-88TF7H0/IdeaProjects/JDBC/JDBC_demo/src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5. 获取数据库连接 Connection
        Connection conn = dataSource.getConnection();

        //2. 定义SQL
        String sql = " delete from tb_brand where id = ?";

        //3. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4. 设置参数

        pstmt.setInt(1,id);

        //5. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);


        //7. 释放资源
        pstmt.close();
        conn.close();

    }
}
